package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import model.FitsFile;
import model.FitsFileList;

public class FitsDao {
	
	
	public FitsFileList getFilesFromDB(ArrayList<String> surveys) throws SQLException{
		
		FitsFileList ffList = new FitsFileList();
		String inClause = "(";
	
		Iterator<String> itr = surveys.iterator();
		int count=0;
		while(itr.hasNext()){
			if(count==0){ inClause = inClause+"\""+itr.next()+"\"";}
			else{ inClause = inClause+","+"\""+itr.next()+"\""; }
			count++;
		}
		inClause = inClause+")";
//System.out.println(inClause);	

		DBCon db = new DBCon();
		Connection con = db.getCon();
		Statement stmt;
		ResultSet rs;
		
			stmt = con.createStatement();
			 rs = stmt.executeQuery("SELECT * FROM fits_files WHERE survey IN "+inClause);
		
		while(rs.next()){
			String folder = rs.getString("folder");
			String file = rs.getString("filename");
			String filePath = folder+file;
			FitsFile ff = new FitsFile(filePath);
			ff.setFilename(rs.getString("filename"));
			ff.setFolder(rs.getString("folder"));
			ff.setSurvey(rs.getString("survey"));
			ff.setTelescope(rs.getString("telescope"));
			ff.setAuthor(rs.getString("author"));
			ff.setObserver(rs.getString("observer"));
			ff.setEquinox(rs.getDouble("equinox"));
			ff.setCtype1(rs.getString("ctype1"));
			ff.setCtype2(rs.getString("ctype2"));
			ff.setCrval1(rs.getDouble("crval1"));
			ff.setCrval2(rs.getDouble("crval2"));
			ffList.addFitsFile(ff);
		}

		return ffList;
	}
	
	public FitsFileList getFilesFromDB(ArrayList<String> surveys, double RA1, double RA2, double DEC1, double DEC2) throws SQLException{
		
		FitsFileList ffList = new FitsFileList();
		String inClause = "(";
	
		Iterator<String> itr = surveys.iterator();
		int count=0;
		while(itr.hasNext()){
			if(count==0){ inClause = inClause+"\""+itr.next()+"\"";}
			else{ inClause = inClause+","+"\""+itr.next()+"\""; }
			count++;
		}
		inClause = inClause+")";
//System.out.println(inClause);	

		DBCon db = new DBCon();
		Connection con = db.getCon();
		Statement stmt;
		ResultSet rs;
		
			stmt = con.createStatement();
			 rs = stmt.executeQuery("SELECT * FROM fits_files WHERE (survey IN "+inClause+") AND (crval1 BETWEEN "+RA1+" AND "+RA2+") AND (crval2 BETWEEN "+DEC1+" AND "+DEC2+")");
		
		while(rs.next()){
			String folder = rs.getString("folder");
			String file = rs.getString("filename");
			String filePath = folder+file;
			FitsFile ff = new FitsFile(filePath);
			ff.setFilename(rs.getString("filename"));
			ff.setFolder(rs.getString("folder"));
			ff.setSurvey(rs.getString("survey"));
			ff.setTelescope(rs.getString("telescope"));
			ff.setAuthor(rs.getString("author"));
			ff.setObserver(rs.getString("observer"));
			ff.setEquinox(rs.getDouble("equinox"));
			ff.setCtype1(rs.getString("ctype1"));
			ff.setCtype2(rs.getString("ctype2"));
			ff.setCrval1(rs.getDouble("crval1"));
			ff.setCrval2(rs.getDouble("crval2"));
			ffList.addFitsFile(ff);
		}

		return ffList;
	}
	
	public void saveFilesToDB(ArrayList<FitsFile> ffList) throws SQLException {
		
			DBCon db = new DBCon();
			Connection con = db.getCon();
			if(con==null){ System.out.println("Connection is null"); }
				else { System.out.println("Connection is not null"); System.out.println(con.toString());}
			Statement stmt = con.createStatement();
		
		
		//iterate through the files and save each to the DB
		Iterator<FitsFile> itr = ffList.iterator();
		while(itr.hasNext()){
			//save each file to the database
			saveSingleFileToDB(itr.next(),stmt);
		}
	}
	
	private void saveSingleFileToDB(FitsFile ff, Statement stmt) throws SQLException{
		String filename = ff.getFilename();
		String folder = ff.getFolder();

		if(java.lang.System.getProperty("os.name").equals("Unix")){ }
		if(java.lang.System.getProperty("os.name").equals("Windows Vista")) { folder = folder.replace("\\", "\\\\");		}

		String telescope = ff.getTelescope();
		if(telescope==null){telescope="";}
		String survey = ff.getSurvey();
		if(survey==null){survey="";}
		String author = ff.getAuthor(); 
		if(author==null){author="";}
		String observer = ff.getObserver();
		if(observer==null){observer="";}
		double equinox = ff.getEquinox();
		String ctype1 = ff.getCtype1();
		if(ctype1==null){ctype1="";}
		String ctype2 = ff.getCtype2();
		if(ctype2==null){ctype2="";}
		double crval1 = ff.getCrval1();
		double crval2 = ff.getCrval2();
		
		
		
		String query = "INSERT INTO fits_files (filename, folder, survey, telescope, author, observer, equinox, ctype1, ctype2, crval1, crval2) VALUES('"+filename+"', '"+folder+"', '"+survey+"', '"+telescope+"', '"+author+"','"+observer+"', "+equinox+", '"+ctype1+"', '"+ctype2+"', "+crval1+","+crval2+")";		
		
			stmt.executeUpdate(query);
		
	}
	
	public ArrayList<FitsFile> getListOfFitsFiles() throws SQLException{
		ArrayList<FitsFile> ffList = new ArrayList<FitsFile>();
		
		DBCon db = new DBCon();
		Connection con = db.getCon();
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM fits_files");
		while(rs.next()){
			String folder = rs.getString("folder");
			String file = rs.getString("filename");
			String filePath = folder+file;
			FitsFile ff = new FitsFile(filePath);
			ff.setFilename(rs.getString("filename"));
			ff.setFolder(rs.getString("folder"));
			ff.setSurvey(rs.getString("survey"));
			ff.setTelescope(rs.getString("telescope"));
			ff.setAuthor(rs.getString("author"));
			ff.setObserver(rs.getString("observer"));
			ff.setEquinox(rs.getDouble("equinox"));
			ff.setCtype1(rs.getString("ctype1"));
			ff.setCtype2(rs.getString("ctype2"));
			ff.setCrval1(rs.getDouble("crval1"));
			ff.setCrval2(rs.getDouble("crval2"));
			ffList.add(ff);
		}
		
		return ffList;
	}
}